// QuestionsCollectedMapper.java
package com.scuec.groupfive.mapper;

import com.scuec.groupfive.entity.QuestionsCollectedEntity;
import com.scuec.groupfive.entity.User;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;

@Mapper
public interface QuestionsCollectedMapper {

    @Select("<script>" +
            "SELECT " +
            "    f.user_id, " +
            "    u.username AS username, " +
            "    f.question_id, " +
            "    q.content AS content, " +
            "    q.question_type AS questionType, " +
            "    et.exam_type AS examType, " +
            "    su.subject_name AS subjectName, " +
            "    ch.chapter_name AS chapterName, " +
            "    GROUP_CONCAT(DISTINCT kp.point_name ORDER BY kp.point_id SEPARATOR ', ') AS pointNames, " +
            "    CASE q.question_type " +
            "        WHEN 'single_choice' THEN cq.answer " +
            "        WHEN 'fill_in_the_blank' THEN JSON_UNQUOTE(JSON_EXTRACT(fbq.blanks, '$[0].answer')) " +
            "        WHEN 'subjective' THEN sq.reference_answer " +
            "        ELSE NULL " +
            "    END AS correctAnswer, " +
            "    CASE q.question_type " +
            "        WHEN 'single_choice' THEN cq.analysis " +
            "        WHEN 'fill_in_the_blank' THEN fbq.analysis " +
            "        WHEN 'subjective' THEN sq.analysis " +
            "        ELSE NULL " +
            "    END AS analysis " +
            "FROM " +
            "    favorites f " +
            "JOIN users u ON f.user_id = u.user_id AND f.user_id = #{userId} " +
            "LEFT JOIN questions q ON f.question_id = q.question_id " +
            "LEFT JOIN subjects su ON q.subject_id = su.subject_id " +
            "LEFT JOIN exam_types et ON q.type_id = et.type_id " +
            "LEFT JOIN chapters ch ON q.chapter_id = ch.chapter_id " +
            "LEFT JOIN question_knowledge qk ON q.question_id = qk.question_id " +
            "LEFT JOIN knowledge_points kp ON qk.point_id = kp.point_id " +
            "LEFT JOIN choice_questions cq ON q.question_id = cq.question_id AND q.question_type = 'single_choice' " +
            "LEFT JOIN fill_blank_questions fbq ON q.question_id = fbq.question_id AND q.question_type = 'fill_in_the_blank' " +
            "LEFT JOIN subjective_questions sq ON q.question_id = sq.question_id AND q.question_type = 'subjective' " +
            "<where>" +
            "   <if test='subjectId != null'>" +
            "       AND su.subject_id = #{subjectId}" +
            "   </if>" +
            "</where>" +
            "GROUP BY " +
            "    f.user_id, " +
            "    u.username, " +
            "    f.question_id, " +
            "    q.content, " +
            "    q.question_type, " +
            "    et.exam_type, " +
            "    su.subject_name, " +
            "    ch.chapter_name, " +
            "    cq.answer, " +
            "    fbq.blanks, " +
            "    sq.reference_answer, " +
            "    cq.analysis, " +
            "    fbq.analysis, " +
            "    sq.analysis " +
            "LIMIT #{offset}, #{limit}" +
            "</script>")
    List<QuestionsCollectedEntity> getCollectedQuestions(
            @Param("userId") Integer userId,
            @Param("subjectId") Integer subjectId,
            @Param("offset") Integer offset,
            @Param("limit") Integer limit);

    @Select("<script>" +
            "SELECT COUNT(DISTINCT f.question_id) " +
            "FROM favorites f " +
            "JOIN users u ON f.user_id = u.user_id AND f.user_id = #{userId} " +
            "LEFT JOIN questions q ON f.question_id = q.question_id " +
            "LEFT JOIN subjects su ON q.subject_id = su.subject_id " +
            "<where>" +
            "   <if test='subjectId != null'>" +
            "       AND su.subject_id = #{subjectId}" +
            "   </if>" +
            "</where>" +
            "</script>")
    int countCollectedQuestions(
            @Param("userId") Integer userId,
            @Param("subjectId") Integer subjectId);

    @Select("SELECT user_id, username FROM users WHERE username = #{username}")
    User searchUser(@Param("username") String username);
}